import pymysql
import os
import time
from db_config import DB_CONFIG

def wait_for_db():
    """等待数据库连接就绪"""
    max_attempts = 30
    for attempt in range(max_attempts):
        try:
            connection = pymysql.connect(
                host=DB_CONFIG['host'],
                user=DB_CONFIG['user'],
                password=DB_CONFIG['password'],
                charset=DB_CONFIG['charset']
            )
            connection.close()
            print("数据库连接成功!")
            return True
        except Exception as e:
            print(f"尝试连接数据库 ({attempt+1}/{max_attempts}): {e}")
            time.sleep(2)
    
    print("无法连接到数据库，初始化失败")
    return False

def init_database():
    """初始化数据库和表"""
    try:
        # 连接到MySQL服务器
        connection = pymysql.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            charset=DB_CONFIG['charset']
        )
        
        with connection.cursor() as cursor:
            # 创建数据库（如果不存在）
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{DB_CONFIG['db']}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
        
        connection.close()
        
        # 连接到指定数据库
        connection = pymysql.connect(**DB_CONFIG)
        
        with connection.cursor() as cursor:
            # 创建标准音频表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS standard_audios (
                id INT AUTO_INCREMENT PRIMARY KEY,
                character_text VARCHAR(10) NOT NULL,
                pinyin VARCHAR(50) NOT NULL,
                tone_type INT NOT NULL,
                audio_path VARCHAR(255) NOT NULL,
                frequency_data TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY (character_text, pinyin)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            
            # 检查是否有数据
            cursor.execute("SELECT COUNT(*) as count FROM standard_audios")
            result = cursor.fetchone()
            
            # 如果没有数据，插入一些示例数据
            if result['count'] == 0:
                print("插入示例数据...")
                cursor.execute("""
                INSERT INTO standard_audios (character_text, pinyin, tone_type, audio_path, frequency_data)
                VALUES 
                ('一', 'yi', 1, 'audio_samples/一_yi_1.wav', '[{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 220.0}]'),
                ('二', 'er', 4, 'audio_samples/二_er_4.wav', '[{"time": 0.1, "frequency": 280.0}, {"time": 0.2, "frequency": 200.0}]'),
                ('三', 'san', 1, 'audio_samples/三_san_1.wav', '[{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 220.0}]'),
                ('四', 'si', 4, 'audio_samples/四_si_4.wav', '[{"time": 0.1, "frequency": 280.0}, {"time": 0.2, "frequency": 200.0}]'),
                ('五', 'wu', 3, 'audio_samples/五_wu_3.wav', '[{"time": 0.1, "frequency": 220.0}, {"time": 0.2, "frequency": 180.0}, {"time": 0.3, "frequency": 240.0}]')
                """)
        
        connection.commit()
        print("数据库初始化完成!")
        return True
        
    except Exception as e:
        print(f"数据库初始化失败: {e}")
        return False
    finally:
        if 'connection' in locals() and connection:
            connection.close()

if __name__ == "__main__":
    if wait_for_db():
        init_database() 